package com.oocl.venus.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.oocl.venus.dao.PersonDao;
import com.oocl.venus.entity.Person;
import com.oocl.venus.utils.DbUtils;



public class PersonDaoImpl implements PersonDao {

	// Person增加记录
	public int addPerson(Person p) {
		String sql = "insert into person(pid,pname,birthday,tel,did,salary) values(per.nextval,?,TO_DATE(?, 'YYYY-MM-DD'),?,?,?)";
		// INSERT INTO "DAIGA"."PERSON" (PID, PNAME, BIRTHDAY, TEL, DID, SALARY)
		// VALUES ('2', 'DDD', TO_DATE('16-JUL-16', 'DD-MON-RR'), '234234', '2',
		// '23123')
		Connection con = null;
		PreparedStatement pst = null;
		con = DbUtils.connect();
		int m = 0;
		try {
			pst = con.prepareStatement(sql);
			con.setAutoCommit(false);
			pst.setString(1, p.getPname());
			pst.setString(2, p.getBirthday());
			pst.setString(3, p.getTel());
			pst.setInt(4, p.getDid());
			pst.setInt(5, p.getSalary());

			m = pst.executeUpdate();
			con.commit();
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (Exception e2) {
				e2.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			DbUtils.free(con, pst, null);
		}

		return m;
	}

	// Person删除记录
	public int deletePerson(int pid) {
		String sql = "delete from person where pid=?";
		Connection con = null;
		PreparedStatement pst = null;
		con = DbUtils.connect();
		int m = 0;
		try {
			pst = con.prepareStatement(sql);
			con.setAutoCommit(false);
			pst.setInt(1, pid);

			m = pst.executeUpdate();
			con.commit();
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (Exception e2) {
				e2.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			DbUtils.free(con, pst, null);
		}

		return m;
	}

	// Person更改记录
	public int updatePerson(Person p) {
		String sql = "update person set pname=?,birthday=TO_DATE(?, 'YYYY-MM-DD'),tel=?,did=?,salary=?where pid=?";
		Connection con = null;
		PreparedStatement pst = null;
		con = DbUtils.connect();
		int m = 0;
		try {
			pst = con.prepareStatement(sql);
			con.setAutoCommit(false);

			pst.setString(1, p.getPname());
			pst.setString(2, p.getBirthday());
			pst.setString(3, p.getTel());
			pst.setInt(4, p.getDid());
			pst.setInt(5, p.getSalary());
			pst.setInt(6, p.getPid());
			
			m = pst.executeUpdate();
			con.commit();
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (Exception e2) {
				e2.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			DbUtils.free(con, pst, null);
		}

		return m;
	}

	// Person显示所有人的所有信息
	public List<Person> showAllPerson() {
		List<Person> cs = new ArrayList<Person>();

		String sql = "select * from person";
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		con = DbUtils.connect();
		try {
			pst = con.prepareStatement(sql);
			rs = pst.executeQuery();
			while (rs.next()) {
				int pid = rs.getInt("pid");
				String pname = rs.getString("pname");
				String birthday = rs.getString("birthday");
				String tel = rs.getString("tel");
				int did = rs.getInt("did");
				int salary = rs.getInt("salary");

				Person p = new Person(pname, birthday, tel, did, salary);
				p.setPid(pid);
				cs.add(p);
			}
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtils.free(con, pst, null);
		}

		return cs;
	}

	// 显示单个人信息
	public Person loadPerson(int pid) {
		Person p = null;
		String sql = "select * from person where pid=?";
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		con = DbUtils.connect();
		try {
			pst = con.prepareStatement(sql);
			pst.setInt(1, pid);
			rs = pst.executeQuery();
			if (rs.next()) {
				String pname = rs.getString("pname");
				String birthday = rs.getString("birthday");
				String tel = rs.getString("tel");
				int did = rs.getInt("did");
				int salary = rs.getInt("salary");

				p = new Person(pname, birthday, tel, did, salary);
				p.setPid(pid);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbUtils.free(con, pst, null);
		}

		return p;
	}

}
